-- TODO: Check if needed
if exists (select 1 from sysobjects where name = 'chk_planabteilung' and type = 'P')
begin
   drop procedure chk_planabteilung
   print 'View: chk_planabteilung deleted ...'
end
go
create procedure chk_planabteilung(
  @atid       int = 1,
  @planid     int = 1
)
as
begin
  set nocount on

   select distinct a.AbteilungID from ArbeitsPlan ap 
    inner join Schemas s
       on s.SchemaID = ap.SchemaID 
    inner join PlanSchema ps 
       on ps.SchemaID = s.SchemaID
    inner join Abteilung a 
       on a.AbteilungID = ps.AbteilungID
    where ap.ATID = @atid
      and ap.PlanID = @planid

end
go
print 'Procedure: chk_planabteilung done ...'
go

grant exec on chk_planabteilung to prsadmins with grant option
go
grant exec on chk_planabteilung to prsusers
go

